﻿CREATE PROCEDURE [usix].[s_Export]
	@UsixID varchar(8),
	@CompanyID int
AS
SET NOCOUNT ON;

EXEC sh_Debug_PRINT 'usix.s_Export $0/$1',@UsixID,@CompanyID

DECLARE @Context int EXEC @Context = sp__Context_ArchiveOff 1

--IF EXISTS(SELECT * FROM t_Loan WHERE MsID = @MsID)BEGIN	RAISERROR('Megasys account %s has already been exported.',16,1,@MsID) RETURN END

DECLARE @LoanID varchar(50),@USIX_VIN varchar(18),@OpenField1 int,@VC bit

SELECT @LoanID = LoanID,@USIX_VIN = ISNULL(VIN,[Serial#]) FROM usix.t_CustomerFile_Export
WHERE [Customer #] = @UsixID AND CompanyID = @CompanyID AND LoanID > 0
IF @LoanID > 0 BEGIN RAISERROR('%s is already associated with %s',16,1,@LoanID,@UsixID) RETURN 0 END

SELECT TOP 1 @USIX_VIN = VIN,@OpenField1 = [Open Field#01] FROM usix.t_CustomerFile_Export
WHERE [Customer #] = @UsixID AND CompanyID = @CompanyID
ORDER BY dbo.VINCheck(VIN) DESC

IF NOT EXISTS(SELECT * FROM usix.t_CustomerFile_Export WHERE [Customer #] = @UsixID AND CompanyID = @CompanyID) BEGIN
	RAISERROR('No records found for Customer # [%s] Company %d in t_CustomerFile_Export',16,1,@UsixID,@CompanyID)	RETURN 0 END

EXEC sh_Debug_PRINT 'BEGIN TRAN Export for $0/$1/$2',@UsixID,@CompanyID,@USIX_VIN
BEGIN TRAN Export

IF NOT EXISTS(SELECT * FROM t_Car WHERE VIN = @USIX_VIN)INSERT INTO t_Car(VIN) VALUES(@USIX_VIN)
UPDATE t_Car SET Year = [Car Year], Make = ms.Make, Model = ms.Model, Mileage = ms.MILEAGE
FROM t_Car c CROSS JOIN t_CustomerFile_Export ms
WHERE c.VIN = @USIX_VIN AND ms.[Customer #] = @UsixID AND CompanyID = @CompanyID

--IF EXISTS(SELECT * FROM t_CarInfo WHERE VIN = @USIX_VIN) BEGIN	ROLLBACK TRAN Export	RAISERROR('VIN [%s] is used more than once.',16,1,@USIX_VIN) RETURN 0 END

/**/EXEC sh_Debug_PRINT 'INSERT INTO t_Loan(StatusID)VALUES(StatusID)'
INSERT INTO t_Loan(StatusID,ApplicantID,CompanyID)VALUES(dbo.fs_StateID__Processing__FundedInUSIX(),@OpenField1,@CompanyID)
SET @LoanID = IDENT_CURRENT('t_Loan')

EXEC sh_Debug_PRINT 's_Export_AddOn $0,$1',@UsixID,@CompanyID,@LoanID
EXEC usix.s_Export_Short_AddOn @UsixID,@CompanyID,@LoanID
IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN 0 END

UPDATE t_CarInfo SET VIN = @USIX_VIN WHERE LoanID = @LoanID AND ISNULL(VIN,'') <> @USIX_VIN
IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN 0 END

UPDATE usix.t_CustomerFile_Export SET ApplID = @LoanID,LoanID = @LoanID WHERE  [Customer #] = @UsixID AND CompanyID = @CompanyID

EXEC sh_Debug_PRINT 'COMMIT TRANSACTION Export'
COMMIT TRANSACTION Export

EXEC sp__Context @Context


RETURN @LoanID

--BEGIN TRAN
----SELECT     UsixID, SSN, [Joint SSN]FROM         usix.t_CustomerFile_ExportWHERE     (UsixID = 'DA117259')
--EXECUTE AS Login='NT AUTHORITY\SYSTEM'
--exec usix.s_Export @UsixID='DA117259',@CompanyID=2
--REVERT
--ROLLBACK TRAN


